const conn = require('../app/database')

class GoodsModel {

  async getGoodsInfo(goodsId) {
    const statement = `
      SELECT 
        g.id goodsId, g.spu_no spuNo, g.goods_name goodsName, 
        g.show_img showImg, g.initial_price initialPrice,
        g.new_price newPrice, g.category_id categoryId, g.theme_id themeId,g.goods_desc goodsDesc
      FROM goods g
      WHERE g.id = ?;
    `
    const [result] = await conn.execute(statement, [goodsId])
		return result

  }

  async getGoodsDetailInfo(goodsId) {
    const statement = `
      SELECT 
        g.id goodsId, g.spu_no spuNo, g.goods_name goodsName, 
        g.show_img showImg, g.initial_price initialPrice,
        g.new_price newPrice, g.category_id categoryId, g.theme_id themeId,g.goods_desc goodsDesc,
        JSON_ARRAYAGG(
          JSON_OBJECT('id', gi.id, 'kind', gi.kind, 'showImg', gi.content)
        ) goodsInfo
      FROM goods g
      LEFT JOIN goods_info gi ON g.id = gi.goods_id
      WHERE g.id = ?;
    `
    const [result] = await conn.execute(statement, [goodsId])
		return result

  }

  async getGoodsSkuDetailInfo(goodsId) {
    const statement = `
      SELECT 
        gs.id id, gs.goods_attr_path goodsAttrPath, gs.stock stock
      FROM goods g 
      LEFT JOIN goods_sku gs ON g.id = gs.goods_id
      WHERE g.id = ?;
    `
    const [result] = await conn.execute(statement, [goodsId])
		return result
  }

  async getGoodsAttrDetailInfo(goodsId) {
    const statement = `
      SELECT 
        gak.id id, gak.attr_key attrKey,
        JSON_ARRAYAGG(
          JSON_OBJECT('id', gav.id, 'attrValue', gav.attr_value, 'attrKeyId', gav.attr_key_id)
        ) goodsAttrValues
      FROM goods_attr_key gak
      LEFT JOIN goods_attr_value gav ON gak.id = gav.attr_key_id
      WHERE gak.goods_id = ?
      GROUP BY gak.id;
    `
    const [result] = await conn.execute(statement, [goodsId])
		return result
  }

  async searchGoodsByKeyword(keyword) {
    const statement = `
      SELECT
        g.id goodsId, g.spu_no spuNo, g.goods_name goodsName, 
        g.show_img showImg, g.initial_price initialPrice,
        g.new_price newPrice, g.category_id categoryId, 
        g.theme_id themeId,g.goods_desc goodsDesc,
        g.createdAt createTime,
        g.updatedAt updateTime
      FROM goods g
      WHERE (goods_name LIKE CONCAT(?, '%')) 
        OR (goods_name LIKE CONCAT('%', ?)) 
        OR (goods_name LIKE CONCAT('%', ? ,'%'));
    `
    const [result] = await conn.execute(statement, [keyword, keyword, keyword])
		return result

  }

}

module.exports = new GoodsModel()